Tiny File Manager | mainLog_maintain_data

Back Back Up Advanced Editor

<?php
//======================================================================================
//
// Function: Read mainLog.sqlite
//
// Programmer: JKJ
// Date      : 2025-02-09
//
// Copyright Reeft A/S (c) - 2025
//======================================================================================

//======================================================================================
// Set start time
//======================================================================================

$starttime = microtime(true);

//======================================================================================
// General config
//======================================================================================
	include "config/config.php";

//======================================================================================
// Get input
//======================================================================================

if (isset($_REQUEST["fromdate"])) {
		$fromdate = $_REQUEST["fromdate"];
	} else {
		$fromdate = '';
	}

if (isset($_REQUEST["fromtime"])) {
		$fromtime = $_REQUEST["fromtime"];
	} else {
		$fromtime = '';
	}

if (isset($_REQUEST["todate"])) {
		$todate = $_REQUEST["todate"];
	} else {
		$todate = '';
	}

if (isset($_REQUEST["totime"])) {
		$totime = $_REQUEST["totime"];
	} else {
		$totime = '';
	}

if (isset($_REQUEST["logType"])) {
		$logType = $_REQUEST["logType"];
	} else {
		$logType = '';
	}

if (isset($_REQUEST["logCode"])) {
		$logCode = $_REQUEST["logCode"];
	} else {
		$logCode = '';
	}

if (isset($_REQUEST["logPgm"])) {
		$logPgm = $_REQUEST["logPgm"];
	} else {
		$logPgm = '';
	}

if (isset($_REQUEST["clientID"])) {
		$clientID = $_REQUEST["clientID"];
	} else {
		$clientID = '';
	}

if (isset($_REQUEST["dataLine"])) {
		$dataLine = $_REQUEST["dataLine"];
	} else {
		$dataLine = '';
	}

//===============================================================
	// Pagination fields "local"/datatables
	//===============================================================

if (isset($_REQUEST["rpyOffSet"])) {
		$rpyOffSet = $_REQUEST["rpyOffSet"];
	} else {
		$rpyOffSet = 0;
	}

if (isset($_REQUEST["rpyPageSize"])) {
		$rpyPageSize = $_REQUEST["rpyPageSize"];
	} else {
		$rpyPageSize = 0;
	}

if (isset($_REQUEST["rpyOrderColumn"])) {
		$rpyOrderColumn = $_REQUEST["rpyOrderColumn"];
	} else {
		$rpyOrderColumn = '';
	}

//======================================================================================
// Get order by
//======================================================================================
	$rpyOrderColumn = str_replace("|", ' ', $rpyOrderColumn);
	$aryOrderBy = explode(',', $rpyOrderColumn);

//======================================================================================
// Get session variables
//======================================================================================
	include "include/getsession.php";
	include "include/sec2hms.php";
	include "include/REEFT_date_convert.php";

//======================================================================================
// Set language
//======================================================================================

include "include/set_language.php";

//======================================================================================
// Set defaults
//======================================================================================
	$a = 1;
	$b = 2;

if ( $a == $b ) {

if ($fromdate == '' ) {
			$fromdate = date("Y-m-d",strtotime("-1 month"));
		}
		if ($fromtime == '' ) {
			$fromtime = date("H:i:s");
		}

if ($todate == '' ) {
			$todate = date("Y-m-d");
		}
		if ($totime == '' ) {
			$totime = date("H:i:s",strtotime("+1 hour"));
		}

}

if ( $fromdate <> '' ) {

$fromTimeStamp 	= $fromdate . ' ' . $fromtime . ':00';
		$toTimeStamp 	= $todate   . ' ' . $totime   . ':59';

$fromTimeStamp_UTC 		= REEFT_local_to_utc( $fromTimeStamp );
		$toTimeStamp_UTC 		= REEFT_local_to_utc( $toTimeStamp );

} else {

$fromTimeStamp_UTC 		= '';
		$toTimeStamp_UTC 		= '';

}

//======================================================================================
// Connect to SQLite database in file
//======================================================================================

$entries_found = 0;

$currentDate = date('Y-m-d');
	$currentTime = date('H:i:s');

$returnCode = '00';
	$returnMsg 	= 'Alles ist gut';

// Init
	$aryDetail = array();

header('Content-Type: application/json;charset=utf-8');

//======================================================================================
// Connect to some DB
//======================================================================================
	include "include/db_connect.php";

//======================================================================================
// Create SQL
//======================================================================================
	$recordsTotal = 0;

// $sql =  "select count(*) as recordsTotal
			// FROM main_log
			// ";

$sql =  "SELECT tableCount as recordsTotal FROM recordCounter WHERE tableName = 'main_Log'";

include "include/db_run_sql.php";

foreach( $data as $row )
	{
		$recordsTotal = $row["recordsTotal"];
	}

// Make integer
	$recordsTotal = intval( $recordsTotal );

// Format it...
	$recordsTotalFormat = number_format($recordsTotal,0,",",".");

//======================================================================================
// 	Create SQL
//	$sql_where = "WHERE timeStamp >= '2025-01-15 00:00:00' and timeStamp <= '2025-01-23 23:59:59'";
//======================================================================================
	if ( $fromdate <> '' ) {

$sql_where = "WHERE timeStamp >= '$fromTimeStamp_UTC' and timeStamp <= '$toTimeStamp_UTC'";
		$sql_where = "WHERE timeStamp BETWEEN '$fromTimeStamp_UTC' and '$toTimeStamp_UTC'";
		
		
		if ( $logType <> '' ) {
			$logType = strtoupper( $logType );
			$sql_where = $sql_where . " " . "AND logType = '$logType'";
		}
		
		if ( $logCode <> '' ) {
			$logCode = strtoupper( $logCode );
			$sql_where = $sql_where . " " . "AND logCode = '$logCode'";
		}
		
		if ( $logPgm <> '' ) {
			//$sql_where = $sql_where . " " . "AND logPgm like '%$logPgm%'";
			$sql_where = $sql_where . " " . "AND logPgm = '$logPgm'";
		}
		
		if ( $clientID <> '' ) {
			//$sql_where = $sql_where . " " . "AND logPgm like '%$logPgm%'";
			$sql_where = $sql_where . " " . "AND clientID like '%$clientID%'";
		}
		if ( $dataLine <> '' ) {
			//$sql_where = $sql_where . " " . "AND logPgm like '%$logPgm%'";
			$sql_where = $sql_where . " " . "AND dataLine like '%$dataLine%'";
		}

}
		else
	{

$sql_where = '';

}

$sql_order = "order by $rpyOrderColumn, pk_key desc";
	$sql_order = "order by timeStamp desc, pk_key desc";

$sql =  "select
				a.pk_key			as a_pk_key,
				a.timeStamp			as a_timeStamp,
				a.logType			as a_logType,
				a.logCode			as a_logCode,
				a.logPgm			as a_logPgm,
				a.dataLine			as a_dataLine,
				a.clientID			as a_clientID
				FROM main_log a
				$sql_where

$sql_order
				LIMIT $rpyPageSize OFFSET $rpyOffSet
	";

//======================================================================================
// Save sql for header
//======================================================================================
	$save_sql = $sql;

// echo $sql;
	// exit;

//======================================================================================
// mySQL or SQLite (default)
//======================================================================================
	include "include/db_run_sql.php";

foreach( $data as $row )
	{

$tmpRow = array();

$tmpRow["pk_key"]				= $row["a_pk_key"];
		$tmpRow["timeStamp"]			= $row["a_timeStamp"];
		$tmpRow["logType"]				= $row["a_logType"];
		$tmpRow["logCode"]				= $row["a_logCode"];
		$tmpRow["logPgm"]				= $row["a_logPgm"];
		$tmpRow["dataLine"]				= $row["a_dataLine"];
		$tmpRow["clientID"]				= $row["a_clientID"];

$timestamp_LOCAL = REEFT_utc_to_local( $tmpRow["timeStamp"] );
		$tmpRow["timeStamp_local"]  	= $timestamp_LOCAL;

$aryDetail[] = $tmpRow;

$entries_found++;

}

//======================================================================================
// Check if data is found
//======================================================================================
	if ( $entries_found == 0 ) {
		$returnCode	= '99';
		$returnMsg 	= 'No data found';
	}

//======================================================================================
// Records filetered
//======================================================================================

$recordsFiltered = $recordsTotal;

if ( $fromdate <> '' ) {
	// $sql =  "select count(*) as recordsTotal
			// FROM main_log
			// ";

$sql =  "select count(*) as recordsFiltered
				FROM main_log
				$sql_where
			";
	include "include/db_run_sql.php";

foreach( $data as $row )
	{
		$recordsFiltered = $row["recordsFiltered"];
	}

}

//======================================================================================
// Calculate response time
//======================================================================================
	$endtime 			= microtime(true);
	$response_time 		= $endtime - $starttime;
	$response_time 		= number_format($response_time, 6, '.', '');
	$response_time_raw 	= number_format($response_time, 6, '.', '');
	$response_time 		= '(' . $response_time . ' seconds)';
	$response_time_raw 	= $response_time_raw;

//======================================================================================
// Create header
//======================================================================================
	$aryHeader = array();
	$aryHeader["rpyCMPNO"]				= '';
	$aryHeader["rpyCMPNO_Name"]	    	= $rpyCMPNO_Name;
	$aryHeader["rpyFile_name"]	    	= 'mainLog';
	$aryHeader["rpyFile_description"]  	= 'Integration mainLog';

$aryHeader["entries_found"] 		= $entries_found;
	$aryHeader["recordsTotal"] 			= $recordsTotal;
	$aryHeader["recordsTotalFormat"] 	= $recordsTotalFormat;
	$aryHeader["recordsFiltered"] 		= $recordsFiltered;
	$aryHeader["currentDate"] 			= $currentDate;
	$aryHeader["currentTime"] 			= $currentTime;
	$aryHeader["returnCode"] 			= $returnCode;
	$aryHeader["returnMsg"] 			= $returnMsg;
	$aryHeader["response_sec"] 			= $response_time;
	$aryHeader["response_sec_raw"] 		= $response_time_raw;

$aryHeader["fromdate"] 				= $fromdate;
	$aryHeader["fromtime"] 				= $fromtime;
	$aryHeader["todate"] 				= $todate;
	$aryHeader["totime"] 				= $totime;

$aryHeader["logType"] 				= $logType;
	$aryHeader["logCode"] 				= $logCode;
	$aryHeader["logPgm"] 				= $logPgm;
	$aryHeader["clientID"] 				= $clientID;
	$aryHeader["dataLine"] 				= $dataLine;

$aryHeader["rpyOffSet"] 			= $rpyOffSet;
	$aryHeader["rpyPageSize"] 			= $rpyPageSize;
	$aryHeader["rpyOrderColumn"] 		= $rpyOrderColumn;

$sql_pretty = str_replace("\r\n", ' ', $save_sql); 		// remove carriage returns
	$sql_pretty = str_replace("\t", ' ', $sql_pretty); 	// remove carriage returns
	$aryHeader["sql"] 					= $sql_pretty;
	$aryHeader["sql_where"] 			= $sql_where;

// Create array and prepare for json encoding
	$returnJson["header"] 				= $aryHeader;
	$returnJson["data"] 				= $aryDetail;

//======================================================================================
// Paint it black
//======================================================================================
    echo(json_encode($returnJson));